package com.hust.bridgecheck.repository;

import com.hust.bridgecheck.entity.DailySensorData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface DailySDataRepository extends JpaRepository<DailySensorData,Integer> {
    @Query(nativeQuery = true, value = "select * from daily_sensor_data where bridgeid = ?1 and report_date > ?2 and report_date <= ?3")
    List<DailySensorData> getAllBridgeData(Integer bridgeId, String startime, String endtime);

    @Query(nativeQuery = true, value = "select id,bridgeid,data_file,sum(data_total_sum) as data_total_sum,group_concat(first_warningid) as first_warningid, sum(first_warning_sum) as first_warning_sum,is_normal,report_date,group_concat(report_type) as report_type,group_concat(second_warningid) as second_warningid, sum(second_warning_sum) as second_warning_sum,group_concat(sensorid) as sensorid,group_concat(warningid) as warningid, sum(warning_sum) as warning_sum,data_flag,remarks from daily_sensor_data where bridgeid = ?1 and data_flag = 0 and report_date > ?2 and report_date <= ?3 group by report_date")
    List<DailySensorData> getBridgeDataByGroupAnd(Integer bridgeId, String startime, String endtime);

    @Query(nativeQuery = true, value = "select id,bridgeid,data_file,sum(data_total_sum) as data_total_sum,group_concat(first_warningid) as first_warningid, sum(first_warning_sum) as first_warning_sum,is_normal,report_date,group_concat(report_type) as report_type,group_concat(second_warningid) as second_warningid, sum(second_warning_sum) as second_warning_sum,group_concat(sensorid) as sensorid,group_concat(warningid) as warningid, sum(warning_sum) as warning_sum, data_flag,remarks from daily_sensor_data where data_flag = ?1 and report_date > ?2 and report_date <= ?3 group by report_date,bridgeid;")
    List<DailySensorData> getDailyReportByFlag(Integer dataFlag,String startime,String endtime);//0表示系统报表，1表示用户表
}

